/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package camada.bancodedados.daoimplemente.cliente;

import camada.bancodedados.conexao.Conexao;
import camada.negocio.dao.ClienteDAO;
import eloma.classe.Cliente;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Icaro
 */
public class PessoaFisicaDAOImpl implements ClienteDAO {

    private static Connection con;
    PreparedStatement stmt = null;
    ResultSet rs = null;

    @Override
    public void inserir(Cliente cliente) throws Exception {

        try {

            con = Conexao.getCon();
            con.setAutoCommit(false);

            final String SQL_SCRIPT = "insert into cliente(tipoPessoa,nome,nomeFantasia,endereco,numero,complemento,bairro,idCidade,estado,cep,observacoes,cnpjCpf,inscricao,outroContato,telefone,telefax,celular,email)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

            stmt = con.prepareStatement(SQL_SCRIPT);
            stmt.setString(1, cliente.getTipoPessoa());
            stmt.setString(2, cliente.getNome());
            stmt.setString(3, cliente.getNomeFantasia());
            stmt.setString(4, cliente.getEndereco());
            stmt.setInt(5, cliente.getNumero());
            stmt.setString(6, cliente.getComplemento());
            stmt.setString(7, cliente.getBairro());
            stmt.setInt(8, cliente.getIdCidade());
            stmt.setString(9, cliente.getEstado());
            stmt.setString(10, String.valueOf(cliente.getCep()));
            stmt.setString(11, cliente.getObs());
            stmt.setString(12, String.valueOf(cliente.getCnpjCpf()));
            stmt.setString(13, String.valueOf(cliente.getIncricao()));
            stmt.setString(14, cliente.getOutrosContato());
            stmt.setString(15, String.valueOf(cliente.getTelefone()));
            stmt.setString(16, String.valueOf(cliente.getTeleFax()));
            stmt.setString(17, String.valueOf(cliente.getCelular()));
            stmt.setString(18, cliente.getEmail());

            stmt.executeUpdate();

            con.commit();

        } catch (SQLException e) {
            con.rollback();
        } catch (Exception e) {
            con.rollback();
        }

    }

    @Override
    public Cliente buscaClienteNome(String nomeCliente) throws Exception {

        try {

            con = Conexao.getCon();
            con.setAutoCommit(false);

            final String SQL_SCRIPT = "select * from cliente where nome = ? ";

            stmt = con.prepareStatement(SQL_SCRIPT);
            stmt.setString(1, nomeCliente);
            rs = stmt.executeQuery();

            con.commit();

        } catch (SQLException sqle) {
            con.rollback();
        } catch (Exception e) {
            con.rollback();
        }

        return carregarResultadoSimples(rs);

    }

    public List<Cliente> buscaClienteLikeNome(String nomeCliente) throws Exception {

        try {
            final String SQL_SCRIPT = "select * from cliente where nome  like '%" + nomeCliente + "%'";

            con = Conexao.getCon();
            con.setAutoCommit(false);

            stmt = con.prepareStatement(SQL_SCRIPT);
            rs = stmt.executeQuery();

            con.commit();

        } catch (SQLException sqle) {
            con.rollback();
        } catch (Exception e) {
            con.rollback();
        }

        return carregarMultiplosResultados(rs);
    }

    @Override
    public Cliente buscaId(int idCliente) throws Exception {

        try {

            final String SQL_SCRIPT = "select * from cliente where idcliente = ?";

            con = Conexao.getCon();
            con.setAutoCommit(false);

            stmt = con.prepareStatement(SQL_SCRIPT);
            stmt.setInt(1, idCliente);
            rs = stmt.executeQuery();

            con.commit();

        } catch (SQLException sqle) {
            con.rollback();
        } catch (Exception e) {
            con.rollback();
        }

        return carregarResultadoSimples(rs);
    }

    @Override
    public void atualizar(Cliente cliente) throws Exception {

        try {

            final String SQL_SCRIPT = "update cliente set tipoPessoa = ?,nome = ?,nomeFantasia = ?,endereco = ?,numero = ?,complemento = ?,bairro = ?,idCidade = ?,estado = ?,cep = ?,observacoes = ?,cnpjCpf = ?,inscricao = ?,outroContato = ?,telefone = ?,telefax = ?,celular = ?,email = ? where idCliente = ?";

            con = Conexao.getCon();
            con.setAutoCommit(false);

            stmt = con.prepareStatement(SQL_SCRIPT);
            stmt.setString(1, cliente.getTipoPessoa());
            stmt.setString(2, cliente.getNome());
            stmt.setString(3, cliente.getNomeFantasia());
            stmt.setString(4, cliente.getEndereco());
            stmt.setInt(5, cliente.getNumero());
            stmt.setString(6, cliente.getComplemento());
            stmt.setString(7, cliente.getBairro());
            stmt.setInt(8, cliente.getIdCidade());
            stmt.setString(9, cliente.getEstado());
            stmt.setString(10, String.valueOf(cliente.getCep()));
            stmt.setString(11, cliente.getObs());
            stmt.setString(12, String.valueOf(cliente.getCnpjCpf()));
            stmt.setString(13, String.valueOf(cliente.getIncricao()));
            stmt.setString(14, cliente.getOutrosContato());
            stmt.setString(15, String.valueOf(cliente.getTelefone()));
            stmt.setString(16, String.valueOf(cliente.getTeleFax()));
            stmt.setString(17, String.valueOf(cliente.getCelular()));
            stmt.setString(18, cliente.getEmail());
            stmt.setInt(19, cliente.getIdCliente());

            stmt.executeUpdate();

            con.commit();

        } catch (SQLException e) {
            con.rollback();
        } catch (Exception e) {
            con.rollback();
        }
    }

    @Override
    public void remover(Cliente cliente) throws Exception {

        try {

            final String SQL_SCRIPT = "delete from cliente where idcliente = ?";

            con = Conexao.getCon();
            con.setAutoCommit(false);

            stmt = con.prepareStatement(SQL_SCRIPT);
            stmt.setInt(1, cliente.getIdCliente());
            stmt.executeUpdate();

            con.commit();

        } catch (SQLException e) {
            con.rollback();
        } catch (Exception e) {
            con.rollback();
        }

    }

    @Override
    public List<Cliente> buscaTodos() throws Exception {

        try {

            String SQL_SCRIPT = "Select * from Cliente order by idCliente";

            con = Conexao.getCon();
            con.setAutoCommit(false);

            stmt = con.prepareStatement(SQL_SCRIPT);
            rs = stmt.executeQuery();

            con.commit();

        } catch (SQLException sqle) {
            con.rollback();
        } catch (Exception e) {
            con.rollback();
        }

        return carregarMultiplosResultados(rs);

    }

    public List<Cliente> carregarMultiplosResultados(ResultSet rs) throws SQLException {
        List<Cliente> resultList = new ArrayList<Cliente>();
        while (rs.next()) {
            Cliente dto = new Cliente();
            carregarVO(dto, rs);
            resultList.add(dto);
        }
        return resultList;
    }

    private Cliente carregarResultadoSimples(ResultSet rs) throws SQLException {
        if (rs.next()) {
            Cliente dto = new Cliente();
            carregarVO(dto, rs);
            return dto;
        } else {
            return null;
        }
    }

    private void carregarVO(Cliente dto, ResultSet rs) throws SQLException {
        dto.setIdCliente(rs.getInt("idcliente"));
        dto.setTipoPessoa(rs.getString("tipoPessoa"));
        dto.setNome(rs.getString("nome"));
        dto.setNomeFantasia(rs.getString("nomeFantasia"));
        dto.setEndereco(rs.getString("endereco"));
        dto.setNumero(rs.getInt("numero"));
        dto.setComplemento(rs.getString("complemento"));
        dto.setBairro(rs.getString("bairro"));
        dto.setIdCidade(rs.getInt("idCidade"));
        dto.setEstado(rs.getString("estado"));
        dto.setCep(rs.getString("cep"));
        dto.setObs(rs.getString("observacoes"));
        dto.setCnpjCpf(rs.getString("cnpjCpf"));
        dto.setIncricao(rs.getString("inscricao"));
        dto.setOutrosContato(rs.getString("outroContato"));
        dto.setTelefone(rs.getString("telefone"));
        dto.setTeleFax(rs.getString("teleFax"));
        dto.setCelular(rs.getString("celular"));
        dto.setEmail(rs.getString("email"));
    }

}
